package dreampack.dataQuery;

import java.sql.*;
import java.util.*;

import dreampack.*;
import dreampack.dataType.*;

public class EmailQuery {
	public EmailQuery() { }
	
	public static EmailType queryById(DreamDB dreamDb, int emaiId)throws Exception{
		EmailType email = null;
		Connection con = null;
		PreparedStatement prepStmt = null;
		ResultSet rs = null;
		
		try{
			con = dreamDb.getConnection();
			String selectStatement = "select *, null as `read_time`, null as `delete_flag` from t_email where `id`=?";
			prepStmt = con.prepareStatement(selectStatement);
			prepStmt.setInt(1, emaiId);
			rs = prepStmt.executeQuery();
			if (rs.next()) {
				email = new EmailType();
				email.init(dreamDb, rs);
				return email;
			}
			else
				return null;
		}finally{
			dreamDb.closeResultSet(rs);
			dreamDb.closeStatement(prepStmt);
			dreamDb.closeConnection(con);
		}
	}
	
	public static Collection querySendedEmailsByUser(DreamDB dreamDb, UserType user) throws Exception {
		ArrayList emails = new ArrayList();
		
		Connection con = null;
		PreparedStatement prepStmt = null;
		ResultSet rs = null;
		int userId = user.getId();
		
		try{
			con = dreamDb.getConnection();
			String selectStatement = "select *, null as `read_time`, null as `delete_flag` from t_email where `from_id`=? and `send_flag`=? order by `create_at`";
			prepStmt = con.prepareStatement(selectStatement);
			prepStmt.setInt(1, userId);
			prepStmt.setBoolean(2, true);
			rs = prepStmt.executeQuery();
			while (rs.next()){
				EmailType email = new EmailType();
				email.init(dreamDb, rs);
				emails.add(email);
			}
		}finally{
			dreamDb.closeResultSet(rs);
			dreamDb.closePrepStmt(prepStmt);
			dreamDb.closeConnection(con);
		}
		//Collections.sort(emails);
		return emails;
	}
	
	public static Collection queryNotSendEmailsByUser(DreamDB dreamDb, UserType user) throws Exception {
		ArrayList emails = new ArrayList();
		
		Connection con = null;
		PreparedStatement prepStmt = null;
		ResultSet rs = null;
		int userId = user.getId();
		
		try{
			con = dreamDb.getConnection();
			String selectStatement = "select *, null as `read_time`, null as `delete_flag` from t_email where `from_id`=? and `send_flag`=? order by `create_at`";
			prepStmt = con.prepareStatement(selectStatement);
			prepStmt.setInt(1, userId);
			prepStmt.setBoolean(2, false);
			rs = prepStmt.executeQuery();
			while (rs.next()){
				EmailType email = new EmailType();
				email.init(dreamDb, rs);
				emails.add(email);
			}
		}finally{
			dreamDb.closeResultSet(rs);
			dreamDb.closePrepStmt(prepStmt);
			dreamDb.closeConnection(con);
		}
		//Collections.sort(emails);
		return emails;
	}
	
	public static Collection queryReceivedEmailsByUser(DreamDB dreamDb, UserType user) throws Exception{
		ArrayList emails = new ArrayList();
		
		Connection con = null;
		PreparedStatement prepStmt = null;
		ResultSet rs = null;
		int userId = user.getId();
		
		try{
			con = dreamDb.getConnection();
			String selectStatement = "select * from t_email where `to_id`=? or `to_id` is null order by `create_at`";
			prepStmt = con.prepareStatement(selectStatement);
			prepStmt.setInt(1, userId);
			rs = prepStmt.executeQuery();
			while (rs.next()){
				EmailType email = new EmailType();
				email.init(dreamDb, rs);
				emails.add(email);
			}
		}finally{
			dreamDb.closeResultSet(rs);
			dreamDb.closePrepStmt(prepStmt);
			dreamDb.closeConnection(con);
		}
		//Collections.sort(emails);
		return emails;
	}
	
	public static Collection queryReadedEmailsByUser(DreamDB dreamDb, UserType user) throws Exception{
		ArrayList emails = new ArrayList();
		
		Connection con = null;
		PreparedStatement prepStmt = null;
		ResultSet rs = null;
		int userId = user.getId();
		
		try{
			con = dreamDb.getConnection();
			String selectStatement = "select e.*, er.read_time, er.delete_flag from t_email e, t_email_readed er where (e.to_id=? or e.to_id is null) and e.id=er.email_id and er.user_id=?";
			prepStmt = con.prepareStatement(selectStatement);
			prepStmt.setInt(1, userId);
			prepStmt.setInt(2, userId);
			rs = prepStmt.executeQuery();
			
			while (rs.next()) {
				EmailType email = new EmailType();
				email.init(dreamDb, rs);
				emails.add(email);
			}
		}finally{
			dreamDb.closeResultSet(rs);
			dreamDb.closeStatement(prepStmt);
			dreamDb.closeConnection(con);
		}
		//Collections.sort(emails);
		return emails;
	}
	
	public static Collection queryNotReadEmailsByUser(DreamDB dreamDb, UserType user) throws Exception{
		ArrayList emails = new ArrayList();
		
		Connection con = null;
		PreparedStatement prepStmt = null;
		ResultSet rs = null;
		int userId = user.getId();
		
		try{
			con = dreamDb.getConnection();
			String selectStatement = "select *, null as `read_time`, null as `delete_flag` from t_email where (`to_id`=? or `to_id` is null) and `id` not in (select `email_id` from t_email_readed where `user_id`=?);";
			prepStmt = con.prepareStatement(selectStatement);
			prepStmt.setInt(1, userId);
			prepStmt.setInt(2, userId);
			rs = prepStmt.executeQuery();
			
			while (rs.next()) {
				EmailType email = new EmailType();
				email.init(dreamDb, rs);
				emails.add(email);
			}
		}finally{
			dreamDb.closeResultSet(rs);
			dreamDb.closeStatement(prepStmt);
			dreamDb.closeConnection(con);
		}
		//Collections.sort(emails);
		return emails;
	}
}
